package com.itcast.demo.excelToJson.test;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelToJsonConverter {

    public static void main(String[] args) {
        String excelFilePath = "C:\\Users\\SuQianLong\\Desktop\\a.xlsx";
        List<Map<String, Object>> excelData = readExcelFile(excelFilePath);
        String jsonData = convertToJson(excelData);
        System.out.println(jsonData);
    }

    private static List<Map<String, Object>> readExcelFile(String filePath) {
        List<Map<String, Object>> dataList = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0); // 读取第一个工作表
            Row headerRow = sheet.getRow(0); // 读取表头

            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i < lastRowNum; i++) { // 从第二行开始读取数据
                Row row = sheet.getRow(i);
                Map<String, Object> rowData = new HashMap<>();

                for (int j = 0; j < headerRow.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    String header = headerRow.getCell(j).getStringCellValue();
                    Object cellValue = getCellValue(cell);
                    rowData.put(header, cellValue);
                }
                dataList.add(rowData);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataList;
    }

    private static Object getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return null;
            default:
                return null;
        }
    }

    private static String convertToJson(List<Map<String, Object>> dataList) {
        ObjectMapper objectMapper = new ObjectMapper();
        try {
            return objectMapper.writeValueAsString(dataList);
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }
}